﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace NS
{
    [HotUpdate]
    [Description("资金汇总表")]
    public class SummaryOfFundsReport : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();

            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "序号";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("资金汇总表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "序号";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "T.XH");

            DateTime FStartDate;
            DateTime FEndDate;
            string FORGID = "", FORGIDSUM="";
            string Forgid_idsum = "";
            string SQL0 = string.Format(@"/*dialect*/
select t0.fuserid, t0.fname, t1.FORGID, t3.FROLEID, t3.FDESCRIPTION  from T_SEC_USER t0 
inner join T_SEC_USERORG t1 on (t0.FUSERID = t1.FUSERID) 
inner join T_SEC_USERROLEMAP t2 on (t1.FENTITYID = t2.FENTITYID) 
inner join T_SEC_ROLE t3 on (t2.FROLEID = t3.FROLEID) 
where t0.fuserid='{0}' and t3.FROLEID =618669
", this.Context.UserId);
            DynamicObjectCollection DynamData0 = DBUtils.ExecuteDynamicObject(this.Context, SQL0);
            if(DynamData0.Count>0)
            {
                for(int i=0; i< DynamData0.Count; i++)
                {
                    FORGID = Convert.ToString(DynamData0[i]["FORGID"]);
                    FORGIDSUM = FORGID + ',' + FORGIDSUM;
                }
                FORGIDSUM = FORGIDSUM.TrimEnd(',');
            }
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            StringBuilder SQLFilter2 = new StringBuilder();
            StringBuilder SQLFilter3 = new StringBuilder();
            StringBuilder SQLFilter4 = new StringBuilder();
            StringBuilder SQLFilter5 = new StringBuilder();
            StringBuilder SQLFilter6 = new StringBuilder();
            StringBuilder SQLFilter7 = new StringBuilder();
            Forgid_idsum = Convert.ToString(dyFilter["F_WNEG_MulSelOrgList"]);
            FStartDate = Convert.ToDateTime(dyFilter["FStartDate"]);//开始日期
            FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//结束
            DateTime firstDayOfMonth = new DateTime(FEndDate.Year, FEndDate.Month, 1);
            DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
            DateTime nextMonth8th = new DateTime(FEndDate.Year, FEndDate.Month, 8).AddMonths(1); // 获取次月的8号
            if(!FORGIDSUM.ToString().Equals(""))
            {
                SQLFilter7.Append("and t.forgid not in ("+ FORGIDSUM + ")");
            }
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter.Append(" and t.FDATE<='" + FEndDate + "'");
            }
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter2.Append(" and t1.F_WNEG_DATE<='" + FEndDate.AddDays(6) + "' and t1.F_WNEG_DATE>='"+ FEndDate + "'");
            }
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter3.Append(" and t1.F_WNEG_DATE<='" + FEndDate.AddDays(14) + "' and t1.F_WNEG_DATE>='" + FEndDate + "'");
            }
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter4.Append(" and t.FDATE>='" + firstDayOfMonth + "' and t.FDATE<='"+ lastDayOfMonth + "'");
            }
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter5.Append(" and t.FDATE<='" + nextMonth8th + "'");
            }
            if (!dyFilter["F_WNEG_CheckBox"].ToString().Equals("True"))
            {
                SQLFilter1.Append(" and t.FDOCUMENTSTATUS='C'");
            }
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter6.Append(" and t1.F_WNEG_DATE<='" + FEndDate+ "'");
            }
            DynamicObjectCollection dyCollection = dyFilter["F_WNEG_MulSelOrgList"] as DynamicObjectCollection;
            //if (!Forgid_idsum.ToString().Equals(""))
            //{
               
            //}
            ////过滤 日期
            //if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            //{
            //    SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            //}
            ////if (!dyFilter.IsNullOrEmpty())
            ////{
            //    if (!Convert.ToString(dyFilter["FMaterialid_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t1.FMATERIALID='" + dyFilter["FMaterialid_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            //    }
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
----------------------------------------固定样式（客户要求）-------------------------------------------------------------------------
CREATE table  #AA(XH INT,FORGID INT,FCOUNTRY varchar(20),FORG varchar(20))
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(1,100119,'泰国','MCPP')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(2,100120,'泰国','天美')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(3,100121,'泰国','泰旺')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(4,100122,'泰国','泰宠')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(5,100123,'泰国','panda')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(6,184111,'泰国','海洋')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(7,184112,'泰国','八爪鱼')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(8,378013,'泰国','海鹰')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(9,100125,'泰国','海狸')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(10,100126,'菲律宾','K2K')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(11,100127,'菲律宾','海豹')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(12,100128,'菲律宾','海螺')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(13,449335,'菲律宾','海蛇')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(14,466197,'新加坡','海兔')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(15,100130,'印尼','考拉')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(16,184108,'印尼','海星')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(17,100124,'印尼','海鲸')
--insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(18,184109,'印尼','海马')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(18,524309,'印尼','海熊')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(19,1084600,'印尼','海龙')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(20,1586192,'印尼','海獭')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(21,100131,'马来','海鲨')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(22,184110,'马来','海象')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(23,235316,'马来','海蜂')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(24,801863,'马来','海帮')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(25,1560209,'马来','海鸥')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(26,100129,'越南','海豚')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(27,385925,'台湾','海狮')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(28,458135,'台湾','海苔')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(29,1301284,'台湾','海贝')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(30,100132,'墨西哥','海雕')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(31,1957283,'墨西哥','海鹏')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(32,1122357,'巴西','海鳄')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(33,1882033,'巴西','海胆')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(34,1984825,'巴西','TYC')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(35,1868744,'中国','腾海')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(36,102413,'','综合部')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(37,410716,'','YCK')

-----------------------------------------------银行转账单转出（提现未到账，转出银行账号的，且是否提现=是，另外加上未审核）------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FAmount)FREALPAYAMOUNTFOR,sum(t1.FAmount*t5.FEXCHANGERATE)BWB 
INTO #YHZZDZC
from T_CN_BANKTRANSBILL t
left join T_CN_BANKTRANSBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FFROMBANKACNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T1.FINCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052  and t.FDOCUMENTSTATUS<>'C' AND   t.FDOCUMENTSTATUS<>'B'  and t.FWithdrawal='1' {2} 
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

-----------------------------------------------诚意赊应付款，次月8号前应还款------------------------------------------------------------------
select t.FAPPLYORGID,sum(FUNPAIDAMOUNT)FCYS INTO #CYS from T_CN_PAYAPPLY t
left join T_CN_PAYAPPLYENTRY t1 on t.fid=t1.fid
WHERE F_WNEG_ASSISTANT='64639ef14d75f7' {7} {3} 
group by t.FAPPLYORGID
-----------------------------------------------本月应付物流费------------------------------------------------------------------
select t.FPAYORGID, (sum(FPAYAMOUNT)-sum(FPAYREAPPLYAMT*t2.FEXCHANGERATE))FWFJE into #YFDWFJE from T_AP_PAYABLE t
left join T_AP_PAYABLEPLAN t1 on t.FID=t1.FID
left join T_AP_PAYABLEFIN t2 on t.FID=t2.FID
where t.FBILLTYPEID='3c6f819d78ac4d5981891956c4595b20' {6} 
group by FPAYORGID
-----------------------------------------------供应商未付，前7天------------------------------------------------------------------
select t.FAPPLYORGID,sum(FUNPAIDAMOUNT)FUNPAIDAMOUNT7 into #FKSQ7 from T_CN_PAYAPPLY t
left join T_CN_PAYAPPLYENTRY t1 on t.fid=t1.fid
where fbillno is not null and t.FBILLTYPEID='78acc0ac3462ba8b11e300bb88ac2143' {4}
group by t.FAPPLYORGID
-----------------------------------------------供应商未付，前15天------------------------------------------------------------------
select t.FAPPLYORGID,sum(FUNPAIDAMOUNT)FUNPAIDAMOUNT15 into #FKSQ15 from T_CN_PAYAPPLY t
left join T_CN_PAYAPPLYENTRY t1 on t.fid=t1.fid
where fbillno is not null and t.FBILLTYPEID='78acc0ac3462ba8b11e300bb88ac2143' {5} 
group by t.FAPPLYORGID
-----------------------------------------------供应商未付，总欠款------------------------------------------------------------------
select t.FAPPLYORGID,sum(FUNPAIDAMOUNT)FUNPAIDAMOUNT into #FKSQZQK from T_CN_PAYAPPLY t
left join T_CN_PAYAPPLYENTRY t1 on t.fid=t1.fid
where fbillno is not null and t.FBILLTYPEID='78acc0ac3462ba8b11e300bb88ac2143' 
group by t.FAPPLYORGID
----------------------------------------银行转账单（转入）(未到账)-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FAmount)FREALPAYAMOUNTFOR,sum(t1.FAmount*
CASE WHEN t5.FEXCHANGERATE IS NULL THEN 1 ELSE t5.FEXCHANGERATE END )BWB  
into #YHZZD 
from T_CN_BANKTRANSBILL t
left join T_CN_BANKTRANSBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FTOBANKACNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T1.FINCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052  and t.FDOCUMENTSTATUS<>'C' and t.FWithdrawal='1' {2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID
----------------------------------------收款单（未到账）-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FRECAMOUNTFOR_E)FREALPAYAMOUNTFOR,case when t.FCURRENCYID=1 then sum(t1.FRECAMOUNTFOR_E*1) else sum(t1.FRECAMOUNTFOR_E*T5.FEXCHANGERATE) end BWB into #SKDTXWDZ from T_AR_RECEIVEBILL t
left join T_AR_RECEIVEBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052 and t.FDOCUMENTSTATUS<>'C'
 and t.FWithdrawal='1' {2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID,t.FCURRENCYID

----------------------------------------提现未到账单据合并-------------------------------------------------------------------------
SELECT * INTO #TXWDZ FROM (
SELECT * FROM #YHZZD
UNION ALL
SELECT * FROM #SKDTXWDZ
)T

----------------------------------------计算提现中未到账-------------------------------------------------------------------------
SELECT FORGID,
--CASE WHEN T.FBANKACNTID = 211501 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [香港舰长-汇丰(美元账户)],
CASE WHEN T.FBANKACNTID = 116966 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [万里汇美金账户(美元账户)],
CASE WHEN T.FBANKACNTID = 795053 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇(美元账户)],
CASE WHEN T.FBANKACNTID = 1539664 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇TOB(美元账户)],
CASE WHEN T.FBANKACNTID = 1256150 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [香港舰长中行(美元账户)],
--CASE WHEN T.FBANKACNTID = 537502 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [万里汇美元理财账户(美元账户)],
--CASE WHEN T.FBANKACNTID = 428828 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [兆诺-稠州银行(美元账户)],
--CASE WHEN T.FBANKACNTID = 428829 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [辽阔号-招行(美元账户)],
CASE WHEN T.FBANKACNTID = 209905 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-工行(美元账户)],
CASE WHEN T.FBANKACNTID = 1152700 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [海狸-招行(美元账户)],
--CASE WHEN T.FBANKACNTID = 411671 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广东华于-农行(人名币)],
CASE WHEN T.FBANKACNTID = 116958 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-工行(人名币)],
CASE WHEN T.FBANKACNTID = 1443072 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [香港舰长-中行(人名币)],
CASE WHEN T.FBANKACNTID = 293419 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-招行(人名币)],
CASE WHEN T.FBANKACNTID = 116959 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [进取号-工行(人名币)],
CASE WHEN T.FBANKACNTID = 293418 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [苍穹号-招行(人名币)],
CASE WHEN T.FBANKACNTID = 237981 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [荣耀号-工行(人名币)],
CASE WHEN T.FBANKACNTID = 1082100 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [南宁舰长-招行(人名币)],
CASE WHEN T.FBANKACNTID = 1256127 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [诺时-中行(人名币)],

CASE WHEN T.FBANKACNTID = 335324 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚-菲律宾比索],
CASE WHEN T.FBANKACNTID = 374885 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚人民币账户],
CASE WHEN T.FBANKACNTID = 510347 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚-越南盾],
CASE WHEN T.FBANKACNTID = 294474 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [寻汇-菲律宾比索],
CASE WHEN T.FBANKACNTID = 805141 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [寻汇-新加坡元],
CASE WHEN T.FBANKACNTID = 294475 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [寻汇-印尼盾],

CASE WHEN T.FBANKACNTID = 795509 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-菲律宾比索],
CASE WHEN T.FBANKACNTID = 797780 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-令吉],
CASE WHEN T.FBANKACNTID = 1069754 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-墨西哥比索],
CASE WHEN T.FBANKACNTID = 797782 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-泰铢账户],
CASE WHEN T.FBANKACNTID = 797783 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-新加坡元],
CASE WHEN T.FBANKACNTID = 797784 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-印尼盾账户],

CASE WHEN T.FBANKACNTID = 116974 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [Ksher账户],
CASE WHEN T.FBANKACNTID = 189977 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [pingpong账户],
CASE WHEN T.FBANKACNTID = 294476 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [pyvio账户],
CASE WHEN T.FBANKACNTID = 116967 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [连连账户],

CASE WHEN T.FBANKACNTID = 1995208 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [锦程万里汇-人民币],
CASE WHEN T.FBANKACNTID = 1995210 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [锦程万里汇-美元],

CASE WHEN T.FBANKACNTID = 1569149 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY202-马币],
CASE WHEN T.FBANKACNTID = 1569150 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY214-马币],
CASE WHEN T.FBANKACNTID = 1541765 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY201-马币],
CASE WHEN T.FBANKACNTID = 1881104 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY213-马币],
CASE WHEN T.FBANKACNTID = 1881266 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY103-马币],
CASE WHEN T.FBANKACNTID = 1541766 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY102-马币],
CASE WHEN T.FBANKACNTID = 1541767 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY102-美元],
--CASE WHEN T.FBANKACNTID = 211501 THEN T.BWB ELSE 0 END BWB,
--CASE WHEN T.FBANKACNTID = 211502 THEN T.BWB ELSE 0 END BWB1,
CASE WHEN T.FBANKACNTID = 116966 THEN T.BWB ELSE 0 END BWB2,
--CASE WHEN T.FBANKACNTID = 428828 THEN T.BWB ELSE 0 END BWB3,
--CASE WHEN T.FBANKACNTID = 428829 THEN T.BWB ELSE 0 END BWB4,
--CASE WHEN T.FBANKACNTID = 411671 THEN T.BWB ELSE 0 END BWB5,
CASE WHEN T.FBANKACNTID = 116958 THEN T.BWB ELSE 0 END BWB6,
CASE WHEN T.FBANKACNTID = 209905 THEN T.BWB ELSE 0 END BWB7,
CASE WHEN T.FBANKACNTID = 293419 THEN T.BWB ELSE 0 END BWB8,
CASE WHEN T.FBANKACNTID = 116959 THEN T.BWB ELSE 0 END BWB9,
CASE WHEN T.FBANKACNTID = 293418 THEN T.BWB ELSE 0 END BWB10,
CASE WHEN T.FBANKACNTID = 237981 THEN T.BWB ELSE 0 END BWB11,
CASE WHEN T.FBANKACNTID = 795053 THEN T.BWB ELSE 0 END BWB12,

CASE WHEN T.FBANKACNTID = 335324 THEN T.BWB ELSE 0 END BWB13,
CASE WHEN T.FBANKACNTID = 374885 THEN T.BWB ELSE 0 END BWB14,
CASE WHEN T.FBANKACNTID = 510347 THEN T.BWB ELSE 0 END BWB15,
CASE WHEN T.FBANKACNTID = 294474 THEN T.BWB ELSE 0 END BWB16,
CASE WHEN T.FBANKACNTID = 805141 THEN T.BWB ELSE 0 END BWB17,
CASE WHEN T.FBANKACNTID = 294475 THEN T.BWB ELSE 0 END BWB18,
CASE WHEN T.FBANKACNTID = 116974 THEN T.BWB ELSE 0 END BWB19,
CASE WHEN T.FBANKACNTID = 189977 THEN T.BWB ELSE 0 END BWB20,
CASE WHEN T.FBANKACNTID = 294476 THEN T.BWB ELSE 0 END BWB21,
CASE WHEN T.FBANKACNTID = 116967 THEN T.BWB ELSE 0 END BWB22,

CASE WHEN T.FBANKACNTID = 795509 THEN T.BWB ELSE 0 END BWB23,
CASE WHEN T.FBANKACNTID = 797780 THEN T.BWB ELSE 0 END BWB24,
CASE WHEN T.FBANKACNTID = 1069754 THEN T.BWB ELSE 0 END BWB25,
CASE WHEN T.FBANKACNTID = 797782 THEN T.BWB ELSE 0 END BWB26,
CASE WHEN T.FBANKACNTID = 797783 THEN T.BWB ELSE 0 END BWB27,
CASE WHEN T.FBANKACNTID = 797784 THEN T.BWB ELSE 0 END BWB28,
CASE WHEN T.FBANKACNTID = 1152700 THEN T.BWB ELSE 0 END BWB29,
CASE WHEN T.FBANKACNTID = 1082100 THEN T.BWB ELSE 0 END BWB30,
CASE WHEN T.FBANKACNTID = 1443072 THEN T.BWB ELSE 0 END BWB31,
CASE WHEN T.FBANKACNTID = 1256127 THEN T.BWB ELSE 0 END BWB32,
CASE WHEN T.FBANKACNTID = 1256150 THEN T.BWB ELSE 0 END BWB33,
CASE WHEN T.FBANKACNTID = 1539664 THEN T.BWB ELSE 0 END BWB34,
CASE WHEN T.FBANKACNTID = 1995208 THEN T.BWB ELSE 0 END BWB35,
CASE WHEN T.FBANKACNTID = 1995210 THEN T.BWB ELSE 0 END BWB36,

CASE WHEN T.FBANKACNTID = 1569149 THEN T.BWB ELSE 0 END BWB37,
CASE WHEN T.FBANKACNTID = 1569150 THEN T.BWB ELSE 0 END BWB38,
CASE WHEN T.FBANKACNTID = 1541765 THEN T.BWB ELSE 0 END BWB39,
CASE WHEN T.FBANKACNTID = 1881104 THEN T.BWB ELSE 0 END BWB40,
CASE WHEN T.FBANKACNTID = 1881266 THEN T.BWB ELSE 0 END BWB41,
CASE WHEN T.FBANKACNTID = 1541766 THEN T.BWB ELSE 0 END BWB42,
CASE WHEN T.FBANKACNTID = 1541767 THEN T.BWB ELSE 0 END BWB43
into #YHZZD1
FROM #TXWDZ T


select FORGID,
--Convert(decimal(18,2),SUM([香港舰长-汇丰(美元账户)])) [香港舰长汇丰美元账户],
Convert(decimal(18,2),SUM([万里汇美金账户(美元账户)]))[万里汇美金账户美元账户],
Convert(decimal(18,2),SUM([凯鑫万里汇(美元账户)]))[凯鑫万里汇美元账户],
Convert(decimal(18,2),SUM([凯鑫万里汇TOB(美元账户)]))[凯鑫万里汇TOB美元账户],
Convert(decimal(18,2),SUM([香港舰长中行(美元账户)]))[香港舰长中行美元账户],
--Convert(decimal(18,2),SUM([万里汇美元理财账户(美元账户)]))[万里汇美元理财账户美元账户],
--Convert(decimal(18,2),SUM([兆诺-稠州银行(美元账户)]))[兆诺稠州银行美元账户],
--Convert(decimal(18,2),SUM([辽阔号-招行(美元账户)]))[辽阔号招行美元账户],
Convert(decimal(18,2),SUM([广州诺时-工行(美元账户)]))[广州诺时工行美元账户],
Convert(decimal(18,2),SUM([海狸-招行(美元账户)]))[海狸招行美元账户],
--Convert(decimal(18,2),SUM([广东华于-农行(人名币)]))[广东华于农行人名币],
Convert(decimal(18,2),SUM([广州诺时-工行(人名币)]))[广州诺时工行人名币],
Convert(decimal(18,2),SUM([香港舰长-中行(人名币)]))[香港舰长中行人名币未提现],
Convert(decimal(18,2),SUM([广州诺时-招行(人名币)]))[广州诺时招行人名币],
Convert(decimal(18,2),SUM([进取号-工行(人名币)]))[进取号工行人名币],
Convert(decimal(18,2),SUM([苍穹号-招行(人名币)]))[苍穹号招行人名币],
Convert(decimal(18,2),SUM([荣耀号-工行(人名币)]))[荣耀号工行人名币],
Convert(decimal(18,2),SUM([南宁舰长-招行(人名币)]))[南宁舰长招行人民币],
Convert(decimal(18,2),SUM([诺时-中行(人名币)]))[诺时中行人民币],
Convert(decimal(18,2),SUM([珊瑚-菲律宾比索]))[珊瑚菲律宾比索],
Convert(decimal(18,2),SUM([珊瑚人民币账户]))[珊瑚人民币账户],
Convert(decimal(18,2),SUM([珊瑚-越南盾]))[珊瑚越南盾],
Convert(decimal(18,2),SUM([寻汇-菲律宾比索]))[寻汇菲律宾比索],
Convert(decimal(18,2),SUM([寻汇-新加坡元]))[寻汇新加坡元],
Convert(decimal(18,2),SUM([寻汇-印尼盾]))[寻汇印尼盾],
Convert(decimal(18,2),SUM([Ksher账户]))[Ksher账户],
Convert(decimal(18,2),SUM([pingpong账户]))[pingpong账户],
Convert(decimal(18,2),SUM([pyvio账户]))[pyvio账户],
Convert(decimal(18,2),SUM([连连账户]))[连连账户],
(Convert(decimal(18,2),SUM([珊瑚-菲律宾比索]))+Convert(decimal(18,2),SUM([珊瑚人民币账户]))+Convert(decimal(18,2),SUM([珊瑚-越南盾])))[珊瑚账户],
(Convert(decimal(18,2),SUM([寻汇-菲律宾比索]))+Convert(decimal(18,2),SUM([寻汇-新加坡元]))+Convert(decimal(18,2),SUM([寻汇-印尼盾])))[寻汇账户],
(Convert(decimal(18,2),SUM([凯鑫万里汇-菲律宾比索]))+Convert(decimal(18,2),SUM([凯鑫万里汇-令吉]))+Convert(decimal(18,2),SUM([凯鑫万里汇-墨西哥比索]))+
Convert(decimal(18,2),SUM([凯鑫万里汇-泰铢账户]))+Convert(decimal(18,2),SUM([凯鑫万里汇-新加坡元]))+Convert(decimal(18,2),SUM([凯鑫万里汇-印尼盾账户])))[凯鑫万里汇],
Convert(decimal(18,2),SUM([锦程万里汇-人民币]))[锦程万里汇人民币],
Convert(decimal(18,2),SUM([锦程万里汇-美元]))[锦程万里汇美元],
Convert(decimal(18,2),SUM([MY202-马币]))[MY202马币],
Convert(decimal(18,2),SUM([MY214-马币]))[MY214马币],
Convert(decimal(18,2),SUM([MY201-马币]))[MY201马币],
Convert(decimal(18,2),SUM([MY213-马币]))[MY213马币],
Convert(decimal(18,2),SUM([MY103-马币]))[MY103马币],
Convert(decimal(18,2),SUM([MY102-马币]))[MY102马币],
Convert(decimal(18,2),SUM([MY102-美元]))[MY102美元],


Convert(decimal(18,2),SUM(BWB2+BWB6+BWB7+BWB8+BWB9+BWB10+BWB11+BWB12+BWB31+BWB32+BWB33+BWB29+BWB30+BWB34+BWB35+BWB36))[未到账合计金额人名币],
Convert(decimal(18,2),SUM(BWB13+BWB14+BWB15+BWB16+BWB17+BWB18+BWB19+BWB20+BWB21+BWB22+BWB23+BWB24+BWB25+BWB26+BWB27+BWB28
+BWB37+BWB38+BWB39+BWB40+BWB41+BWB42+BWB43))[未到账合计金额人名币2]
INTO #YHZZD2
from #YHZZD1
GROUP BY FORGID

----------------------------------------开户申请单----------------------------------------------
select FAPPLYORGID,sum(FBorrowAmount)FBorrowAmount,sum(FLendOutAmount)FLendOutAmount INTO #KH from t_CN_OpenApply 
where FDOCUMENTSTATUS='C'
group by FAPPLYORGID

SELECT 组织1,SUM(FREALRECAMOUNT)FREALRECAMOUNT INTO #SK FROM (
----------------------------------------开户申请单（计算期初借入）----------------------------------------------
select T1.FNAME 组织1,sum(T.FBorrowAmount)FREALRECAMOUNT  from t_CN_OpenApply  T
LEFT JOIN T_ORG_ORGANIZATIONS_L T1 ON T.FAPPLYORGID=T1.FORGID
where FDOCUMENTSTATUS='C'
group by T1.FNAME

union all
----------------------------------------收款单(往来单位为综合部)-------------------------------------------------------------------------
select DISTINCT t2.fname 组织1,sum(t1.FREALRECAMOUNT)FREALRECAMOUNT   from T_AR_RECEIVEBILL t
left join T_AR_RECEIVEBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_BD_CURRENCY t4 on t.FCURRENCYID=t4.FCURRENCYID
--left join #KH t5 on t.FPAYORGID=t5.FAPPLYORGID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null 
and t.FCONTACTUNITTYPE='ORG_Organizations' and t.fdate>='2024-07-01'
and t.FCONTACTUNIT='102413'  and t.FAORB='1' {3}
group by t2.fname

union all

----------------------------------------付款单（往来单位为综合部借入）-------------------------------------------------------------------------
select DISTINCT t2.fname 组织2,-sum(t1.FREALPAYAMOUNT)FREALPAYAMOUNT  from T_AP_PAYBILL t
left join T_AP_PAYBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
--left join #KH t4 on t.FPAYORGID=t4.FAPPLYORGID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null 
and t.FCONTACTUNITTYPE='ORG_Organizations' and t.fdate>='2024-07-01'
and  t.FCONTACTUNIT='102413' and t.FAORB='2' {3}
group by t2.fname

)T GROUP BY 组织1




SELECT 组织2,SUM(FREALPAYAMOUNT)FREALPAYAMOUNT INTO #FK FROM (
----------------------------------------开户申请单（计算期初借出）----------------------------------------------
select T1.FNAME 组织2,sum(FLendOutAmount)FREALPAYAMOUNT from t_CN_OpenApply T
LEFT JOIN T_ORG_ORGANIZATIONS_L T1 ON T.FAPPLYORGID=T1.FORGID
where FDOCUMENTSTATUS='C'
group by T1.FNAME

UNION ALL
----------------------------------------付款单（往来单位为综合部）-------------------------------------------------------------------------
select DISTINCT t2.fname 组织2,sum(t1.FREALPAYAMOUNT)FREALPAYAMOUNT  from T_AP_PAYBILL t
left join T_AP_PAYBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
--left join #KH t4 on t.FPAYORGID=t4.FAPPLYORGID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null 
and t.FCONTACTUNITTYPE='ORG_Organizations' and t.fdate>='2024-07-01'
and  t.FCONTACTUNIT='102413' and t.FAORB='1' {3}
group by t2.fname

union all

----------------------------------------收款单(往来单位为综合部)-------------------------------------------------------------------------
select DISTINCT t2.fname 组织1,-sum(t1.FREALRECAMOUNT)FREALRECAMOUNT   from T_AR_RECEIVEBILL t
left join T_AR_RECEIVEBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_BD_CURRENCY t4 on t.FCURRENCYID=t4.FCURRENCYID
--left join #KH t5 on t.FPAYORGID=t5.FAPPLYORGID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null 
and t.FCONTACTUNITTYPE='ORG_Organizations' and t.fdate>='2024-07-01'
and t.FCONTACTUNIT='102413'  and t.FAORB='2' {3}
group by t2.fname
) T GROUP BY 组织2








----------------------------------------付款申请-------------------------------------------------------------------------
select t2.FNAME 组织3,Convert(decimal(18,2),SUM((t1.FUNPAIDAMOUNT)))WFJE INTO #FKSQ from T_CN_PAYAPPLY t
left join T_CN_PAYAPPLYENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FAPPLYORGID=t2.FORGID
WHERE T2.FLOCALEID=2052 {3}  AND t1.FUNPAIDAMOUNT>0 and  t.FBILLTYPEID='78acc0ac3462ba8b11e300bb88ac2143' 
{2}
GROUP BY t2.FNAME

----------------------------------------付款单-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FREALPAYAMOUNTFOR)FREALPAYAMOUNTFOR,sum(t1.FREALPAYAMOUNTFOR*t5.FEXCHANGERATE)BWB into #A1 from T_AP_PAYBILL t
left join T_AP_PAYBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052  {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

----------------------------------------收款单-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FREALRECAMOUNTFOR)FREALPAYAMOUNTFOR,sum(t1.FREALRECAMOUNTFOR*T5.FEXCHANGERATE)BWB into #A2 from T_AR_RECEIVEBILL t
left join T_AR_RECEIVEBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052 {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID


----------------------------------------银行存款期初-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FEntAmount)FREALPAYAMOUNTFOR,sum(t1.FEntAmount*t5.FEXCHANGERATE)BWB into #A3 from T_CN_BANKACNTBALINIT t
left join T_CN_BANKACNTBALINITENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FBankAcntId=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T1.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052  {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID


----------------------------------------银行转账单（转入）-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FAmount)FREALPAYAMOUNTFOR,sum(t1.FAmount*t5.FEXCHANGERATE)BWB into #A4 from T_CN_BANKTRANSBILL t
left join T_CN_BANKTRANSBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FTOBANKACNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T1.FINCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052
  {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID


----------------------------------------银行转账单（转出）-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FTOTALAMOUNT)FREALPAYAMOUNTFOR,sum(t1.FTOTALAMOUNT*t5.FEXCHANGERATE)BWB into #A5 from T_CN_BANKTRANSBILL t
left join T_CN_BANKTRANSBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FFROMBANKACNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T1.FOUTCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052
 {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

----------------------------------------银行转账单（转出体现未到账）-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FTOTALAMOUNT)FREALPAYAMOUNTFOR,sum(t1.FTOTALAMOUNT*t5.FEXCHANGERATE)BWB into #A50 from T_CN_BANKTRANSBILL t
left join T_CN_BANKTRANSBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FFROMBANKACNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T1.FOUTCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052
AND t.FDOCUMENTSTATUS='B' AND T.FWITHDRAWAL='1' {2} 
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID


----------------------------------------收款退款单-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FREALREFUNDAMOUNTFOR)FREALPAYAMOUNTFOR,sum(t1.FREALREFUNDAMOUNTFOR*t5.FEXCHANGERATE)BWB into #A6 from T_AR_REFUNDBILL t
left join T_AR_REFUNDBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052 {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

----------------------------------------付款退款单-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FREALREFUNDAMOUNTFOR)FREALPAYAMOUNTFOR,sum(t1.FREALREFUNDAMOUNTFOR*t5.FEXCHANGERATE)BWB into #A7 from T_AP_REFUNDBILL t
left join T_AP_REFUNDBILLENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052 {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

----------------------------------------手工日记账（借方）-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FDEBITAMOUNT)FREALPAYAMOUNTFOR,sum(t1.FDEBITAMOUNT*t5.FEXCHANGERATE)BWB into #A8 from T_CN_JOURNAL t
left join T_CN_JOURNALENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FBANKACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052   {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

----------------------------------------手工日记账（贷方）-------------------------------------------------------------------------
select DISTINCT t2.fname,T4.FNAME fnumber,t2.FORGID,T4.FBANKACNTID,sum(t1.FCREDITAMOUNT)FREALPAYAMOUNTFOR,sum(t1.FCREDITAMOUNT*t5.FEXCHANGERATE)BWB into #A9 from T_CN_JOURNAL t
left join T_CN_JOURNALENTRY t1 on t.fid=t1.fid
left join T_ORG_ORGANIZATIONS_L t2 on t.FPAYORGID=t2.FORGID
left join T_CN_BANKACNT t3 on t1.FBANKACCOUNTID=t3.FBANKACNTID
left join T_CN_BANKACNT_L t4 on t3.FBANKACNTID=t4.FBANKACNTID
LEFT JOIN (
SELECT e1.FCYFORID, e1.FEXCHANGERATE, e1.FBegDate
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
) T5 ON T.FCURRENCYID=t5.FCYFORID
WHERE T2.FLOCALEID=2052 and t3.fnumber is not null AND T4.FLOCALEID=2052   {3}{2}
group by t2.fname,t3.fnumber,T4.FNAME,t2.FORGID,T4.FBANKACNTID

SELECT DISTINCT * into  #B1 FROM (
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A1
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A2
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A3
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A4
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A5
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A6
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A7
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A8
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A9
UNION ALL
SELECT FNAME,FNUMBER,FORGID,FBANKACNTID FROM #A50
)T
SELECT T.FNAME,T.FNUMBER,T.FORGID,T.FBANKACNTID,
CASE WHEN T5.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T5.FREALPAYAMOUNTFOR END JE1,
CASE WHEN T1.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T1.FREALPAYAMOUNTFOR END JE2,
CASE WHEN T2.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T2.FREALPAYAMOUNTFOR END JE3,
CASE WHEN T3.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T3.FREALPAYAMOUNTFOR END JE4,
CASE WHEN T4.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T4.FREALPAYAMOUNTFOR END JE5,
CASE WHEN T6.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T6.FREALPAYAMOUNTFOR END JE6,
CASE WHEN T7.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T7.FREALPAYAMOUNTFOR END JE7,
CASE WHEN T8.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T8.FREALPAYAMOUNTFOR END JE8,
CASE WHEN T9.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T9.FREALPAYAMOUNTFOR END JE9,
CASE WHEN T50.FREALPAYAMOUNTFOR IS NULL THEN 0 ELSE T50.FREALPAYAMOUNTFOR END JE50,
CASE WHEN T1.BWB IS NULL THEN 0 ELSE T1.BWB END BWB1,
CASE WHEN T2.BWB IS NULL THEN 0 ELSE T2.BWB END BWB2,
CASE WHEN T3.BWB IS NULL THEN 0 ELSE T3.BWB END BWB3,
CASE WHEN T4.BWB IS NULL THEN 0 ELSE T4.BWB END BWB4,
CASE WHEN T5.BWB IS NULL THEN 0 ELSE T5.BWB END BWB5,
CASE WHEN T6.BWB IS NULL THEN 0 ELSE T6.BWB END BWB6,
CASE WHEN T7.BWB IS NULL THEN 0 ELSE T7.BWB END BWB7,
CASE WHEN T8.BWB IS NULL THEN 0 ELSE T8.BWB END BWB8,
CASE WHEN T9.BWB IS NULL THEN 0 ELSE T9.BWB END BWB9,
CASE WHEN T50.BWB IS NULL THEN 0 ELSE T50.BWB END BWB50
INTO #B2 FROM #B1 T
LEFT JOIN #A2 T1 ON T.FORGID=T1.FORGID AND T.FBANKACNTID=T1.FBANKACNTID
LEFT JOIN #A4 T2 ON T.FORGID=T2.FORGID AND T.FBANKACNTID=T2.FBANKACNTID
LEFT JOIN #A1 T3 ON T.FORGID=T3.FORGID AND T.FBANKACNTID=T3.FBANKACNTID
LEFT JOIN #A5 T4 ON T.FORGID=T4.FORGID AND T.FBANKACNTID=T4.FBANKACNTID
LEFT JOIN #A3 T5 ON T.FORGID=T5.FORGID AND T.FBANKACNTID=T5.FBANKACNTID
LEFT JOIN #A6 T6 ON T.FORGID=T6.FORGID AND T.FBANKACNTID=T6.FBANKACNTID
LEFT JOIN #A7 T7 ON T.FORGID=T7.FORGID AND T.FBANKACNTID=T7.FBANKACNTID
LEFT JOIN #A8 T8 ON T.FORGID=T8.FORGID AND T.FBANKACNTID=T8.FBANKACNTID
LEFT JOIN #A9 T9 ON T.FORGID=T9.FORGID AND T.FBANKACNTID=T9.FBANKACNTID
LEFT JOIN #A50 T50 ON T.FORGID=T50.FORGID AND T.FBANKACNTID=T50.FBANKACNTID


SELECT FNAME,FNUMBER,FORGID,FBANKACNTID,(JE1+JE2+JE3-JE4-JE5-JE6+JE7+JE8-JE9-JE50)FREALPAYAMOUNTFOR,
(BWB1+BWB2-BWB3-BWB4+BWB5-BWB6+BWB7+BWB8-BWB9-BWB50)BWB INTO #A FROM #B2



SELECT t.fname 项目,
CASE WHEN T.FBANKACNTID = 116962 THEN ISNULL(T.FREALPAYAMOUNTFOR,0) ELSE 0 END 万里汇,
CASE WHEN T.FBANKACNTID = 795051 THEN ISNULL(T.FREALPAYAMOUNTFOR,0) ELSE 0 END [凯鑫万里汇-人民币],
CASE WHEN T.FBANKACNTID = 116958 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-工行],
CASE WHEN T.FBANKACNTID = 1443072 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [香港舰长-中行人民币],
CASE WHEN T.FBANKACNTID = 523993 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-工行理财],
CASE WHEN T.FBANKACNTID = 293419 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-招行],
CASE WHEN T.FBANKACNTID = 1256127 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州诺时-中行],
CASE WHEN T.FBANKACNTID = 1363085 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [诺时-民泰],
CASE WHEN T.FBANKACNTID = 116964 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [兆诺-稠州银行],
CASE WHEN T.FBANKACNTID = 411671 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广东华于-农行],
CASE WHEN T.FBANKACNTID = 411857 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州辽阔号-招行],
CASE WHEN T.FBANKACNTID = 116961 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [泉州湛蓝号-中信],
CASE WHEN T.FBANKACNTID = 300842 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [吴秋红-支付宝],
CASE WHEN T.FBANKACNTID = 143513 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [徐权龙-招行卡],
CASE WHEN T.FBANKACNTID = 210049 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [谢伟贤-平安卡],
CASE WHEN T.FBANKACNTID = 238222 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [谢伟贤-招行卡],
CASE WHEN T.FBANKACNTID = 238223 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [赵鹏伟-平安卡],
--CASE WHEN T.FBANKACNTID = 209097 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [平台佳/Susie],
CASE WHEN T.FBANKACNTID = 237983 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州辽阔号-中行],
CASE WHEN T.FBANKACNTID = 238225 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州舰长-工行],
CASE WHEN T.FBANKACNTID = 1082100 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [南宁舰长-招行],
CASE WHEN T.FBANKACNTID = 237981 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州荣耀号-工行],
CASE WHEN T.FBANKACNTID = 116959 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州进取号-工行],
CASE WHEN T.FBANKACNTID = 237982 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广东海狸-招行],
CASE WHEN T.FBANKACNTID = 293418 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州苍穹号-招行],
CASE WHEN T.FBANKACNTID = 417200 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广东腾海-招行],
CASE WHEN T.FBANKACNTID = 792891 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [丰顺金晨],
CASE WHEN T.FBANKACNTID = 335324 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚-菲律宾比索],
CASE WHEN T.FBANKACNTID = 374885 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚人民币账户],
CASE WHEN T.FBANKACNTID = 510347 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚-越南盾],
CASE WHEN T.FBANKACNTID = 1363259 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [珊瑚],
CASE WHEN T.FBANKACNTID = 294474 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [寻汇-菲律宾比索],
CASE WHEN T.FBANKACNTID = 805141 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [寻汇-新加坡元],
CASE WHEN T.FBANKACNTID = 294475 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [寻汇-印尼盾],
CASE WHEN T.FBANKACNTID = 116974 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [Ksher账户],
CASE WHEN T.FBANKACNTID = 189977 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [pingpong账户],
CASE WHEN T.FBANKACNTID = 294476 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [pyvio账户],
CASE WHEN T.FBANKACNTID = 116967 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [连连账户],
CASE WHEN T.FBANKACNTID = 795509 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-菲律宾比索],
CASE WHEN T.FBANKACNTID = 797780 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-令吉],
CASE WHEN T.FBANKACNTID = 1069754 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-墨西哥比索],
CASE WHEN T.FBANKACNTID = 797782 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-泰铢账户],
CASE WHEN T.FBANKACNTID = 797783 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-新加坡元],
CASE WHEN T.FBANKACNTID = 797784 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [凯鑫万里汇-印尼盾账户],
CASE WHEN T.FBANKACNTID = 1122449 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [广州舰长工行理财],
CASE WHEN T.FBANKACNTID = 1122450 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [进取号工行理财],
CASE WHEN T.FBANKACNTID = 1122451 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [荣耀号工行理财],
CASE WHEN T.FBANKACNTID = 1597889 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [腾海招行理财],
--CASE WHEN T.FBANKACNTID = 211501 THEN ISNULL(T.FREALPAYAMOUNTFOR,0)+ISNULL(T1.FREALPAYAMOUNTFOR,0) ELSE 0 END [香港舰长-汇丰],
CASE WHEN T.FBANKACNTID = 116966 THEN ISNULL(T.FREALPAYAMOUNTFOR,0)+ISNULL(T1.FREALPAYAMOUNTFOR,0) ELSE 0 END [万里汇美金账户],
CASE WHEN T.FBANKACNTID = 795053 THEN ISNULL(T.FREALPAYAMOUNTFOR,0)+ISNULL(T1.FREALPAYAMOUNTFOR,0) ELSE 0 END [凯鑫万里汇美元],
CASE WHEN T.FBANKACNTID = 1539664 THEN ISNULL(T.FREALPAYAMOUNTFOR,0)+ISNULL(T1.FREALPAYAMOUNTFOR,0) ELSE 0 END [凯鑫万里汇TOB美元],
CASE WHEN T.FBANKACNTID = 537502 THEN ISNULL(T.FREALPAYAMOUNTFOR,0)+ISNULL(T1.FREALPAYAMOUNTFOR,0) ELSE 0 END [万里汇美金理财账户],
CASE WHEN T.FBANKACNTID = 1256150 THEN ISNULL(T.FREALPAYAMOUNTFOR,0)+ISNULL(T1.FREALPAYAMOUNTFOR,0) ELSE 0 END [香港舰长中行],
--CASE WHEN T.FBANKACNTID = 146880 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [诚意赊],
CASE WHEN T.FBANKACNTID = 860397 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [舰长688诚意赊],
CASE WHEN T.FBANKACNTID = 860398 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [舰长988诚意赊],
CASE WHEN T.FBANKACNTID = 116958 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [香港舰长-中行],
CASE WHEN T.FBANKACNTID = 1995208 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [锦程万里汇2-人民币],
CASE WHEN T.FBANKACNTID = 1995210 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [锦程万里汇2-美元],
CASE WHEN T.FBANKACNTID = 1569149 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY202-马币2],
CASE WHEN T.FBANKACNTID = 1569150 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY214-马币2],
CASE WHEN T.FBANKACNTID = 1541765 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY201-马币2],
CASE WHEN T.FBANKACNTID = 1881104 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY213-马币2],
CASE WHEN T.FBANKACNTID = 1881266 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY103-马币2],
CASE WHEN T.FBANKACNTID = 1541766 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY102-马币2],
CASE WHEN T.FBANKACNTID = 1541767 THEN T.FREALPAYAMOUNTFOR ELSE 0 END [MY102-美元2],

CASE WHEN T.FBANKACNTID = 335324 THEN T.BWB ELSE 0 END [珊瑚-菲律宾比索BWB],
CASE WHEN T.FBANKACNTID = 374885 THEN T.BWB ELSE 0 END [珊瑚人民币账户BWB],
CASE WHEN T.FBANKACNTID = 510347 THEN T.BWB ELSE 0 END [珊瑚-越南盾BWB],
CASE WHEN T.FBANKACNTID = 1363259 THEN T.BWB ELSE 0 END [珊瑚BWB],
CASE WHEN T.FBANKACNTID = 294474 THEN T.BWB ELSE 0 END [寻汇-菲律宾比索BWB],
CASE WHEN T.FBANKACNTID = 805141 THEN T.BWB ELSE 0 END [寻汇-新加坡元BWB],
CASE WHEN T.FBANKACNTID = 294475 THEN T.BWB ELSE 0 END [寻汇-印尼盾BWB],
CASE WHEN T.FBANKACNTID = 116974 THEN T.BWB ELSE 0 END [Ksher账户BWB],
CASE WHEN T.FBANKACNTID = 189977 THEN T.BWB ELSE 0 END [pingpong账户BWB],
CASE WHEN T.FBANKACNTID = 294476 THEN T.BWB ELSE 0 END [pyvio账户BWB],
CASE WHEN T.FBANKACNTID = 116967 THEN T.BWB ELSE 0 END [连连账户BWB],
CASE WHEN T.FBANKACNTID = 795509 THEN T.BWB ELSE 0 END [凯鑫万里汇-菲律宾比索BWB],
CASE WHEN T.FBANKACNTID = 797780 THEN T.BWB ELSE 0 END [凯鑫万里汇-令吉BWB],
CASE WHEN T.FBANKACNTID = 1069754 THEN T.BWB ELSE 0 END [凯鑫万里汇-墨西哥比索BWB],
CASE WHEN T.FBANKACNTID = 797782 THEN T.BWB ELSE 0 END [凯鑫万里汇-泰铢账户BWB],
CASE WHEN T.FBANKACNTID = 797783 THEN T.BWB ELSE 0 END [凯鑫万里汇-新加坡元BWB],
CASE WHEN T.FBANKACNTID = 797784 THEN T.BWB ELSE 0 END [凯鑫万里汇-印尼盾账户BWB],
CASE WHEN T.FBANKACNTID = 1569149 THEN T.BWB ELSE 0 END [MY202-马币BWB],
CASE WHEN T.FBANKACNTID = 1569150 THEN T.BWB ELSE 0 END [MY214-马币BWB],
CASE WHEN T.FBANKACNTID = 1541765 THEN T.BWB ELSE 0 END [MY201-马币BWB],
CASE WHEN T.FBANKACNTID = 1881104 THEN T.BWB ELSE 0 END [MY213-马币BWB],
CASE WHEN T.FBANKACNTID = 1881266 THEN T.BWB ELSE 0 END [MY103-马币BWB],
CASE WHEN T.FBANKACNTID = 1541766 THEN T.BWB ELSE 0 END [MY102-马币BWB],
CASE WHEN T.FBANKACNTID = 1541767 THEN T.BWB ELSE 0 END [MY102-美元BWB]
--T.BWB
INTO #B
FROM #A T 
LEFT JOIN #YHZZDZC T1 ON T.FBANKACNTID=T1.FBANKACNTID and t.fname=t1.fname

select 项目,
Convert(decimal(18,2),sum(万里汇))万里汇,
Convert(decimal(18,2),sum([凯鑫万里汇-人民币]))[凯鑫万里汇人民币],
Convert(decimal(18,2),sum([广州诺时-工行]))[广州诺时工行],
Convert(decimal(18,2),sum([香港舰长-中行人民币]))[香港舰长中行人民币],
Convert(decimal(18,2),sum([广州诺时-工行理财]))[广州诺时工行理财],
Convert(decimal(18,2),sum([广州诺时-招行]))[广州诺时招行],
Convert(decimal(18,2),sum([广州诺时-中行]))[广州诺时中行],
Convert(decimal(18,2),sum([诺时-民泰]))[诺时民泰],
Convert(decimal(18,2),sum([兆诺-稠州银行]))[兆诺稠州银行],
Convert(decimal(18,2),sum([广东华于-农行]))[广东华于农行],
Convert(decimal(18,2),sum([广州辽阔号-招行]))[广州辽阔号招行],
Convert(decimal(18,2),sum([泉州湛蓝号-中信]))[泉州湛蓝号中信],
Convert(decimal(18,2),sum([吴秋红-支付宝]))[吴秋红支付宝],
Convert(decimal(18,2),sum([徐权龙-招行卡]))[徐权龙招行卡],
Convert(decimal(18,2),sum([谢伟贤-平安卡]))[谢伟贤平安卡],
Convert(decimal(18,2),sum([谢伟贤-招行卡]))[谢伟贤招行卡],
Convert(decimal(18,2),sum([赵鹏伟-平安卡]))[赵鹏伟平安卡],
--Convert(decimal(18,2),sum([平台佳/Susie]))[平台佳Susie],
Convert(decimal(18,2),sum([广州辽阔号-中行]))[广州辽阔号中行],
Convert(decimal(18,2),sum([广州舰长-工行]))[广州舰长工行],
Convert(decimal(18,2),sum([南宁舰长-招行]))[南宁舰长招行],
Convert(decimal(18,2),sum([广州荣耀号-工行]))[广州荣耀号工行],
Convert(decimal(18,2),sum([广州进取号-工行]))[广州进取号工行],
Convert(decimal(18,2),sum([广东海狸-招行]))[广东海狸招行],
Convert(decimal(18,2),sum([广州苍穹号-招行]))[广州苍穹号招行],
Convert(decimal(18,2),sum([广东腾海-招行]))[广东腾海招行],
Convert(decimal(18,2),sum([丰顺金晨]))[丰顺金晨],
Convert(decimal(18,2),SUM([珊瑚-菲律宾比索]))[珊瑚菲律宾比索余额],
Convert(decimal(18,2),SUM([珊瑚人民币账户]))[珊瑚人民币账户余额],
Convert(decimal(18,2),SUM([珊瑚-越南盾]))[珊瑚越南盾余额],
Convert(decimal(18,2),SUM([珊瑚]))[珊瑚],
Convert(decimal(18,2),SUM([寻汇-菲律宾比索]))[寻汇菲律宾比索余额],
Convert(decimal(18,2),SUM([寻汇-新加坡元]))[寻汇新加坡元余额],
Convert(decimal(18,2),SUM([寻汇-印尼盾]))[寻汇印尼盾余额],
Convert(decimal(18,2),SUM([Ksher账户]))[Ksher账户余额],
Convert(decimal(18,2),SUM([pingpong账户]))[pingpong账户余额],
Convert(decimal(18,2),SUM([pyvio账户]))[pyvio账户余额],
Convert(decimal(18,2),SUM([连连账户]))[连连账户余额],
--Convert(decimal(18,2),sum([香港舰长-汇丰]))[香港舰长汇丰],
Convert(decimal(18,2),sum([凯鑫万里汇TOB美元]))[凯鑫万里汇TOB美元],
Convert(decimal(18,2),sum([香港舰长中行]))[香港舰长中行],
Convert(decimal(18,2),sum([万里汇美金账户]))[万里汇美金账户],
Convert(decimal(18,2),sum([凯鑫万里汇美元]))[凯鑫万里汇美元],
Convert(decimal(18,2),sum([万里汇美金理财账户]))[万里汇美金理财账户],
Convert(decimal(18,2),sum([凯鑫万里汇-菲律宾比索]))[凯鑫万里汇菲律宾比索],
Convert(decimal(18,2),sum([凯鑫万里汇-令吉]))[凯鑫万里汇令吉],
Convert(decimal(18,2),sum([凯鑫万里汇-墨西哥比索]))[凯鑫万里汇墨西哥比索],
Convert(decimal(18,2),sum([凯鑫万里汇-泰铢账户]))[凯鑫万里汇泰铢账户],
Convert(decimal(18,2),sum([凯鑫万里汇-新加坡元]))[凯鑫万里汇新加坡元],
Convert(decimal(18,2),sum([凯鑫万里汇-印尼盾账户]))[凯鑫万里汇印尼盾账户],
Convert(decimal(18,2),sum([广州舰长工行理财]))[广州舰长工行理财],
Convert(decimal(18,2),sum([进取号工行理财]))[进取号工行理财],
Convert(decimal(18,2),sum([荣耀号工行理财]))[荣耀号工行理财],
Convert(decimal(18,2),sum([腾海招行理财]))[腾海招行理财],
--Convert(decimal(18,2),sum([诚意赊]))[诚意赊],
Convert(decimal(18,2),sum([舰长688诚意赊]))[舰长688诚意赊],
Convert(decimal(18,2),sum([舰长988诚意赊]))[舰长988诚意赊],
--Convert(decimal(18,2),sum(BWB))BWB
Convert(decimal(18,2),sum([珊瑚-菲律宾比索BWB]))[珊瑚菲律宾比索BWB],
Convert(decimal(18,2),sum([珊瑚人民币账户BWB]))[珊瑚人民币账户BWB],
Convert(decimal(18,2),sum([珊瑚-越南盾BWB]))[珊瑚越南盾BWB],
Convert(decimal(18,2),sum([珊瑚BWB]))[珊瑚BWB],
Convert(decimal(18,2),sum([寻汇-菲律宾比索BWB]))[寻汇菲律宾比索BWB],
Convert(decimal(18,2),sum([寻汇-新加坡元BWB]))[寻汇新加坡元BWB],
Convert(decimal(18,2),sum([寻汇-印尼盾BWB]))[寻汇印尼盾BWB],
Convert(decimal(18,2),sum([Ksher账户BWB]))[Ksher账户BWB],
Convert(decimal(18,2),sum([pingpong账户BWB]))[pingpong账户BWB],
Convert(decimal(18,2),sum([pyvio账户BWB]))[pyvio账户BWB],
Convert(decimal(18,2),sum([连连账户BWB]))[连连账户BWB],
Convert(decimal(18,2),sum([凯鑫万里汇-菲律宾比索BWB]))[凯鑫万里汇菲律宾比索BWB],
Convert(decimal(18,2),sum([凯鑫万里汇-令吉BWB]))[凯鑫万里汇令吉BWB],
Convert(decimal(18,2),sum([凯鑫万里汇-墨西哥比索BWB]))[凯鑫万里汇墨西哥比索BWB],
Convert(decimal(18,2),sum([凯鑫万里汇-泰铢账户BWB]))[凯鑫万里汇泰铢账户BWB],
Convert(decimal(18,2),sum([凯鑫万里汇-新加坡元BWB]))[凯鑫万里汇新加坡元BWB],
Convert(decimal(18,2),sum([凯鑫万里汇-印尼盾账户BWB]))[凯鑫万里汇印尼盾账户BWB],
Convert(decimal(18,2),sum([锦程万里汇2-人民币]))[锦程万里汇人民币2],
Convert(decimal(18,2),sum([锦程万里汇2-美元]))[锦程万里汇美元2],
Convert(decimal(18,2),sum([MY202-马币2]))[MY202马币2],
Convert(decimal(18,2),sum([MY214-马币2]))[MY214马币2],
Convert(decimal(18,2),sum([MY201-马币2]))[MY201马币2],
Convert(decimal(18,2),sum([MY213-马币2]))[MY213马币2],
Convert(decimal(18,2),sum([MY103-马币2]))[MY103马币2],
Convert(decimal(18,2),sum([MY102-马币2]))[MY102马币2],
Convert(decimal(18,2),sum([MY102-美元2]))[MY102美元2],
Convert(decimal(18,2),sum([MY202-马币BWB]))[MY202马币BWB],
Convert(decimal(18,2),sum([MY214-马币BWB]))[MY214马币BWB],
Convert(decimal(18,2),sum([MY201-马币BWB]))[MY201马币BWB],
Convert(decimal(18,2),sum([MY213-马币BWB]))[MY213马币BWB],
Convert(decimal(18,2),sum([MY103-马币BWB]))[MY103马币BWB],
Convert(decimal(18,2),sum([MY102-马币BWB]))[MY102马币BWB],
Convert(decimal(18,2),sum([MY102-美元BWB]))[MY102美元BWB]
into #C
from #B
group by 项目

SELECT  *,Convert(decimal(18,2),(万里汇+[凯鑫万里汇人民币]+[广州诺时工行]+[广州诺时招行]+[广州诺时中行]+[诺时民泰]+
[兆诺稠州银行]+[吴秋红支付宝]+[徐权龙招行卡]+[香港舰长中行人民币]+
[谢伟贤平安卡]+[谢伟贤招行卡]+[赵鹏伟平安卡]+
[广州荣耀号工行]+[广州进取号工行]+[广东海狸招行]+[广州苍穹号招行]+[广东腾海招行]+[广州舰长工行]+[南宁舰长招行]+[丰顺金晨]+[锦程万里汇人民币2]))人名币小计,

Convert(decimal(18,2),[珊瑚菲律宾比索余额]+[珊瑚人民币账户余额]+[珊瑚越南盾余额]+[珊瑚])珊瑚账户余额,
Convert(decimal(18,2),[寻汇菲律宾比索余额]+[寻汇新加坡元余额]+[寻汇印尼盾余额])寻汇账户余额,
Convert(decimal(18,2),[凯鑫万里汇菲律宾比索]+[凯鑫万里汇令吉]+[凯鑫万里汇墨西哥比索]+[凯鑫万里汇泰铢账户]+[凯鑫万里汇新加坡元]+[凯鑫万里汇印尼盾账户])凯鑫万里汇余额,

Convert(decimal(18,2),[珊瑚菲律宾比索BWB]+[珊瑚人民币账户BWB]+[珊瑚越南盾BWB]+[珊瑚BWB]+
[寻汇菲律宾比索BWB]+[寻汇新加坡元BWB]+[寻汇印尼盾BWB]+
[Ksher账户BWB]+[pingpong账户BWB]+[pyvio账户BWB]+[连连账户BWB]+
[凯鑫万里汇菲律宾比索BWB]+[凯鑫万里汇令吉BWB]+[凯鑫万里汇墨西哥比索BWB]+
[凯鑫万里汇泰铢账户BWB]+[凯鑫万里汇新加坡元BWB]+[凯鑫万里汇印尼盾账户BWB]+
[MY202马币BWB]+[MY214马币BWB]+[MY201马币BWB]+[MY213马币BWB]+[MY103马币BWB]+
[MY102马币BWB]+[MY102美元BWB])平台账户人民币合计,

Convert(decimal(18,2),[珊瑚菲律宾比索余额]+[珊瑚人民币账户余额]+[珊瑚越南盾余额]+[珊瑚]+
[寻汇菲律宾比索余额]+[寻汇新加坡元余额]+[寻汇印尼盾余额]+
[Ksher账户余额]+[pingpong账户余额]+[pyvio账户余额]+[连连账户余额]+
[凯鑫万里汇菲律宾比索]+[凯鑫万里汇令吉]+[凯鑫万里汇墨西哥比索]+
[凯鑫万里汇泰铢账户]+[凯鑫万里汇新加坡元]+[凯鑫万里汇印尼盾账户]+
[MY202马币2]+[MY214马币2]+[MY201马币2]+[MY213马币2]+[MY103马币2]+[MY102马币2]+[MY102美元2])本币合计已到账平台,

Convert(decimal(18,2),([万里汇美金账户]+[凯鑫万里汇美元]+[香港舰长中行]+[凯鑫万里汇TOB美元]+[锦程万里汇美元2]))美元小计,

Convert(decimal(18,2),([广州诺时工行理财]+[广州舰长工行理财]+[进取号工行理财]+[荣耀号工行理财]+[腾海招行理财])+
Convert(decimal(18,2),[万里汇美金理财账户])*(SELECT  e1.FEXCHANGERATE
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
where e1.FCYFORID=7))理财人民币小计,


(Convert(decimal(18,2),([锦程万里汇人民币2]+万里汇+[凯鑫万里汇人民币]+[广州诺时工行]+[广州诺时招行]+[广州诺时中行]+[诺时民泰]+
[兆诺稠州银行]+[吴秋红支付宝]+[徐权龙招行卡]+[香港舰长中行人民币]+
[谢伟贤平安卡]+[谢伟贤招行卡]+[赵鹏伟平安卡]+
[广州荣耀号工行]+[广州进取号工行]+[广东海狸招行]+[广州苍穹号招行]+[广东腾海招行]+[广州舰长工行]+[南宁舰长招行]+[丰顺金晨]))+
Convert(decimal(18,2),([万里汇美金账户]+[凯鑫万里汇美元]+[香港舰长中行]+[凯鑫万里汇TOB美元]+[锦程万里汇美元2]))*(SELECT  e1.FEXCHANGERATE
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
where e1.FCYFORID=7)
+
Convert(decimal(18,2),([广州诺时工行理财]+[广州舰长工行理财]+[进取号工行理财]+[荣耀号工行理财]+[腾海招行理财])+
Convert(decimal(18,2),[万里汇美金理财账户])*(SELECT  e1.FEXCHANGERATE
FROM T_BD_RATE e1
JOIN (
SELECT FCYFORID, MAX(FBegDate) as max_date
FROM T_BD_RATE
GROUP BY FCYFORID
) e2 ON e1.FCYFORID = e2.FCYFORID AND e1.FBegDate = e2.max_date
where e1.FCYFORID=7))
)本位币,
ISNULL(Convert(decimal(18,2),t1.FREALRECAMOUNT),0) 收款单,ISNULL(Convert(decimal(18,2),t2.FREALPAYAMOUNT),0) 付款单 INTO #AAA
FROM #C t
left join #SK t1 on t.项目=t1.组织1
left join #FK t2 on t.项目=t2.组织2
left join #FKSQ t3 on t.项目=组织3

SELECT T.XH AS 序号, t.FCOUNTRY,T.FORG, t1.*,
--ISNULL(T2.[香港舰长汇丰美元账户],0)[香港舰长汇丰美元账户],
ISNULL(T2.[万里汇美金账户美元账户],0)[万里汇美金账户美元账户],
ISNULL(T2.[凯鑫万里汇美元账户],0)[凯鑫万里汇美元账户],
ISNULL(T2.[凯鑫万里汇TOB美元账户],0)[凯鑫万里汇TOB美元账户],
ISNULL(T2.[香港舰长中行美元账户],0)[香港舰长中行美元账户],
--T2.[万里汇美元理财账户美元账户],
--ISNULL(T2.[兆诺稠州银行美元账户],0)[兆诺稠州银行美元账户],
--ISNULL(T2.[辽阔号招行美元账户],0)[辽阔号招行美元账户],
ISNULL(T2.[广州诺时工行美元账户],0)[广州诺时工行美元账户],
ISNULL(T2.[海狸招行美元账户],0)[海狸招行美元账户],
--ISNULL(T2.[广东华于农行人名币],0)[广东华于农行人名币],
ISNULL(T2.[广州诺时工行人名币],0)[广州诺时工行人名币],
ISNULL(T2.[香港舰长中行人名币未提现],0)[香港舰长中行人名币未提现],
ISNULL(T2.[广州诺时招行人名币],0)[广州诺时招行人名币],
ISNULL(T2.[进取号工行人名币],0)[进取号工行人名币],
ISNULL(T2.[苍穹号招行人名币],0)[苍穹号招行人名币],
ISNULL(T2.[荣耀号工行人名币],0)[荣耀号工行人名币],
ISNULL(T2.[南宁舰长招行人民币],0)[南宁舰长招行人民币],
ISNULL(T2.[诺时中行人民币],0)[诺时中行人民币],
--ISNULL(T2.[珊瑚菲律宾比索],0)[珊瑚菲律宾比索],
--ISNULL(T2.[珊瑚人民币账户],0)[珊瑚人民币账户],
--ISNULL(T2.[珊瑚越南盾],0)[珊瑚越南盾],
--ISNULL(T2.[寻汇菲律宾比索],0)[寻汇菲律宾比索],
--ISNULL(T2.[寻汇新加坡元],0)[寻汇新加坡元],
--ISNULL(T2.[寻汇印尼盾],0)[寻汇印尼盾],
ISNULL(T2.[珊瑚账户],0)[珊瑚账户],
ISNULL(T2.[寻汇账户],0)[寻汇账户],
ISNULL(T2.[凯鑫万里汇],0)[凯鑫万里汇],
ISNULL(T2.[Ksher账户],0)[Ksher账户],
ISNULL(T2.[pingpong账户],0)[pingpong账户],
ISNULL(T2.[pyvio账户],0)[pyvio账户],
ISNULL(T2.[连连账户],0)[连连账户],
ISNULL(T2.[锦程万里汇人民币],0)[锦程万里汇人民币],
ISNULL(T2.[锦程万里汇美元],0)[锦程万里汇美元],

ISNULL(T2.[MY202马币],0)[MY202马币],
ISNULL(T2.[MY214马币],0)[MY214马币],
ISNULL(T2.[MY201马币],0)[MY201马币],
ISNULL(T2.[MY213马币],0)[MY213马币],
ISNULL(T2.[MY103马币],0)[MY103马币],
ISNULL(T2.[MY102马币],0)[MY102马币],
ISNULL(T2.[MY102美元],0)[MY102美元],

ISNULL(T2.[未到账合计金额人名币],0)[未到账合计金额人名币],
ISNULL(T2.[未到账合计金额人名币2],0)[未到账合计金额人名币2],
(ISNULL(T2.[珊瑚账户],0)+ISNULL(T2.[寻汇账户],0)+ISNULL(T2.[凯鑫万里汇],0)+ISNULL(T2.[Ksher账户],0)+ISNULL(T2.[pingpong账户],0)
+ISNULL(T2.[pyvio账户],0)+ISNULL(T2.[连连账户],0)
+ISNULL(T2.[MY202马币],0)+ISNULL(T2.[MY214马币],0)+ISNULL(T2.[MY201马币],0)+ISNULL(T2.[MY213马币],0)
+ISNULL(T2.[MY103马币],0)+ISNULL(T2.[MY102马币],0)+ISNULL(T2.[MY102美元],0))本币合计未到账,

ISNULL(Convert(decimal(18,2),T3.FUNPAIDAMOUNT7),0)FUNPAIDAMOUNT7,
ISNULL(Convert(decimal(18,2),T4.FUNPAIDAMOUNT15),0)FUNPAIDAMOUNT15,
ISNULL(Convert(decimal(18,2),T5.FUNPAIDAMOUNT),0)FUNPAIDAMOUNT,
ISNULL(Convert(decimal(18,2),T6.FWFJE),0)FWFJE,
ISNULL(Convert(decimal(18,2),T7.FCYS),0)FCYS,
ISNULL(Convert(decimal(18,2),T1.本位币),0)本位币合计,
'' 空白列,
'' 空白列1,
'' 空白列2,
'' 空白列3,
'' 空白列4,
'' 空白列5,
{0} into {1}
FROM #AA T
LEFT JOIN #AAA T1 ON T.FORG=T1.项目
LEFT JOIN #YHZZD2 T2 ON T.FORGID=T2.FORGID
LEFT JOIN #FKSQ7 T3 ON T.FORGID=T3.FAPPLYORGID
LEFT JOIN #FKSQ15 T4 ON T.FORGID=T4.FAPPLYORGID
LEFT JOIN #FKSQZQK T5 ON T.FORGID=T5.FAPPLYORGID
LEFT JOIN #YFDWFJE T6 ON T.FORGID=T6.FPAYORGID
LEFT JOIN #CYS T7 ON T.FORGID=T7.FAPPLYORGID
where t.forgid in ({9}) {10}
                                        ", seqFld, tableName, SQLFilter, SQLFilter1, SQLFilter2, SQLFilter3, SQLFilter4, SQLFilter5, SQLFilter6, Forgid_idsum, SQLFilter7);
                                        DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName) 
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        //protected override void ExecuteBatch(List listSql) { base.ExecuteBatch(listSql); }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            var XH00 = header.AddChild("序号", new LocaleValue("序号"));
            XH00.ColIndex = 0;
            XH00.Width = 50;
            var XH01 = header.AddChild("FCOUNTRY", new LocaleValue("国家"));
            XH01.ColIndex = 1;
            XH01.Width = 50;
            var XH02 = header.AddChild("FORG", new LocaleValue("项目"));
            XH02.ColIndex = 2;
            XH02.Width = 50;
            var XH0302 = header.AddChild("锦程万里汇人民币2", new LocaleValue("R锦程-万里汇"), SqlStorageType.SqlDecimal);
            XH0302.ColIndex = 3;
            XH0302.Width = 100;
            var XH0301 = header.AddChild("凯鑫万里汇人民币", new LocaleValue("R凯鑫-万里汇"), SqlStorageType.SqlDecimal);
            XH0301.ColIndex = 4;
            XH0301.Width = 100;
            var XH03 = header.AddChild("万里汇", new LocaleValue("R舰长-万里汇"), SqlStorageType.SqlDecimal);
            XH03.ColIndex = 5;
            XH03.Width = 100;
            var XH0404 = header.AddChild("香港舰长中行人民币", new LocaleValue("R香港舰长-中行"), SqlStorageType.SqlDecimal);
            XH0404.ColIndex = 6;
            XH0404.Width = 90;
            var XH04 = header.AddChild("广州诺时工行", new LocaleValue("R诺时-工行"), SqlStorageType.SqlDecimal);
            XH04.ColIndex = 7;
            XH04.Width = 90;
            var XH05 = header.AddChild("广州诺时招行", new LocaleValue("R诺时-招行"), SqlStorageType.SqlDecimal);
            XH05.ColIndex = 8;
            XH05.Width = 90;
            var XH0402 = header.AddChild("诺时民泰", new LocaleValue("R诺时-民泰"), SqlStorageType.SqlDecimal);
            XH0402.ColIndex = 9;
            XH0402.Width = 90;
            var XH0401 = header.AddChild("广州诺时中行", new LocaleValue("R诺时-中行"), SqlStorageType.SqlDecimal);
            XH0401.ColIndex = 10;
            XH0401.Width = 90;
            var XH171 = header.AddChild("广州舰长工行", new LocaleValue("R广州舰长-工行"), SqlStorageType.SqlDecimal);
            XH171.ColIndex = 11;
            var XH1712 = header.AddChild("南宁舰长招行", new LocaleValue("R南宁舰长-招行"), SqlStorageType.SqlDecimal);
            XH1712.ColIndex = 12;
            var XH18 = header.AddChild("广州荣耀号工行", new LocaleValue("R荣耀号-工行"), SqlStorageType.SqlDecimal);
            XH18.ColIndex = 13;
            var XH19 = header.AddChild("广州进取号工行", new LocaleValue("R进取号-工行"), SqlStorageType.SqlDecimal);
            XH19.ColIndex = 14;
            var XH21 = header.AddChild("广州苍穹号招行", new LocaleValue("R苍穹号-招行"), SqlStorageType.SqlDecimal);
            XH21.ColIndex = 15;
            var XH20 = header.AddChild("广东海狸招行", new LocaleValue("R海狸-招行"), SqlStorageType.SqlDecimal);
            XH20.ColIndex = 16;
            var XH22 = header.AddChild("广东腾海招行", new LocaleValue("R腾海-招行"), SqlStorageType.SqlDecimal);
            XH22.ColIndex = 17;
            var XH220 = header.AddChild("丰顺金晨", new LocaleValue("R金晨-工行"), SqlStorageType.SqlDecimal);
            XH220.ColIndex = 18;
            var XH06 = header.AddChild("兆诺稠州银行", new LocaleValue("R兆诺-稠州银行"), SqlStorageType.SqlDecimal);
            XH06.ColIndex = 19;
            XH06.Width = 110;
            //var XH07 = header.AddChild("广东华于农行", new LocaleValue("R广东华于-农行"), SqlStorageType.SqlDecimal);
            //XH07.ColIndex = 8;
            //var XH09 = header.AddChild("泉州湛蓝号中信", new LocaleValue("R泉州湛蓝号-中信"), SqlStorageType.SqlDecimal);
            //XH09.ColIndex = 9;
            var XH11 = header.AddChild("吴秋红支付宝", new LocaleValue("R吴秋红-支付宝"), SqlStorageType.SqlDecimal);
            XH11.ColIndex = 20;
            var XH12 = header.AddChild("徐权龙招行卡", new LocaleValue("R徐权龙-招行卡"), SqlStorageType.SqlDecimal);
            XH12.ColIndex = 21;
            var XH13 = header.AddChild("谢伟贤平安卡", new LocaleValue("R谢伟贤-平安卡"), SqlStorageType.SqlDecimal);
            XH13.ColIndex = 22;
            var XH14 = header.AddChild("谢伟贤招行卡", new LocaleValue("R谢伟贤-招行卡"), SqlStorageType.SqlDecimal);
            XH14.ColIndex = 23;
            var XH15 = header.AddChild("赵鹏伟平安卡", new LocaleValue("R赵鹏伟-平安卡"), SqlStorageType.SqlDecimal);
            XH15.ColIndex = 24;
            //var XH16 = header.AddChild("平台佳Susie", new LocaleValue("R平台佳/Susie"), SqlStorageType.SqlDecimal);
            //XH16.ColIndex = 16;
            //var XH08 = header.AddChild("广州辽阔号招行", new LocaleValue("R广州辽阔号-招行"), SqlStorageType.SqlDecimal);
            //XH08.ColIndex = 17;
            //XH08.Width = 150;
            //var XH17 = header.AddChild("广州辽阔号中行", new LocaleValue("R广州辽阔号-中行"), SqlStorageType.SqlDecimal);
            //XH17.ColIndex = 18;
            var XH23 = header.AddChild("人名币小计", new LocaleValue("RMB小计1"), SqlStorageType.SqlDecimal);
            XH23.ColIndex = 25;
            var XH242 = header.AddChild("空白列", new LocaleValue(""));
            XH242.ColIndex = 26;
            XH242.Width = 20;

            var XU262 = header.AddChild("锦程万里汇美元2", new LocaleValue("U锦程-万里汇"), SqlStorageType.SqlDecimal);
            XU262.ColIndex = 27;
            var XU26 = header.AddChild("万里汇美金账户", new LocaleValue("U舰长-万里汇"), SqlStorageType.SqlDecimal);
            XU26.ColIndex = 28;
            var XU2601 = header.AddChild("凯鑫万里汇TOB美元", new LocaleValue("U凯鑫-万里汇TOB"), SqlStorageType.SqlDecimal);
            XU2601.ColIndex = 29;
            XU2601.Width = 120;
            var XU260 = header.AddChild("凯鑫万里汇美元", new LocaleValue("U凯鑫-万里汇"), SqlStorageType.SqlDecimal);
            XU260.ColIndex = 30;
            //var XH25 = header.AddChild("香港舰长汇丰", new LocaleValue("U舰长-汇丰"), SqlStorageType.SqlDecimal);
            //XH25.ColIndex = 26;
            var XU261 = header.AddChild("香港舰长中行", new LocaleValue("U香港舰长-中行"), SqlStorageType.SqlDecimal);
            XU261.ColIndex = 31;
            //var XU331 = header.AddChild("万里汇美金理财账户", new LocaleValue("U万里汇美元理财账户"), SqlStorageType.SqlDecimal);
            //XU331.ColIndex = 300;
            var XU27 = header.AddChild("美元小计", new LocaleValue("USD小计2"), SqlStorageType.SqlDecimal);
            XU27.ColIndex = 32;
            var XH280 = header.AddChild("空白列5", new LocaleValue(""));
            XH280.ColIndex = 33;
            XH280.Width = 20;

            var XU28 = header.AddChild("本位币合计", new LocaleValue("R合计(小计1+2+3)"), SqlStorageType.SqlDecimal);
            XU28.ColIndex = 34;
            XU28.Width = 120;
            var XU29 = header.AddChild("付款单", new LocaleValue("借出"), SqlStorageType.SqlDecimal);
            XU29.ColIndex = 35;
            var XU30 = header.AddChild("收款单", new LocaleValue("借入"), SqlStorageType.SqlDecimal);
            XU30.ColIndex = 36;
            var XH2411 = header.AddChild("空白列1", new LocaleValue(""));
            XH2411.ColIndex = 37;
            XH2411.Width = 20;

            var XU39 = header.AddChild("FUNPAIDAMOUNT7", new LocaleValue("最近7天"), SqlStorageType.SqlDecimal);
            XU39.ColIndex = 38;
            XU39.Width = 80;
            var XU40 = header.AddChild("FUNPAIDAMOUNT15", new LocaleValue("最近15天"), SqlStorageType.SqlDecimal);
            XU40.ColIndex = 39;
            XU40.Width = 80;
            var XU41 = header.AddChild("FUNPAIDAMOUNT", new LocaleValue("应付供应商合计"), SqlStorageType.SqlDecimal);
            XU41.ColIndex = 40;
            var XU42 = header.AddChild("FWFJE", new LocaleValue("本月应付物流费"), SqlStorageType.SqlDecimal);
            XU42.ColIndex = 41;
            //var XU43 = header.AddChild("诚意赊", new LocaleValue("诚意赊"), SqlStorageType.SqlDecimal);
            //XU43.ColIndex = 500;
            //XU43.Width = 150;
            var XU4301 = header.AddChild("舰长688诚意赊", new LocaleValue("诚意赊-舰长688"), SqlStorageType.SqlDecimal);
            XU4301.ColIndex = 42;
            XU4301.Width = 120;
            var XU431 = header.AddChild("舰长988诚意赊", new LocaleValue("诚意赊-舰长988"), SqlStorageType.SqlDecimal);
            XU431.ColIndex = 43;
            XU431.Width = 120;
            var XU430 = header.AddChild("空白列3", new LocaleValue(""), SqlStorageType.SqlDecimal);
            XU430.ColIndex = 44;
            XU430.Width = 20;

            //var XU31 = header.AddChild("香港舰长汇丰美元账户", new LocaleValue("U香港舰长-汇丰"), SqlStorageType.SqlDecimal);
            //XU31.ColIndex = 360;
            var XU33 = header.AddChild("万里汇美金账户美元账户", new LocaleValue("U舰长-万里汇"), SqlStorageType.SqlDecimal);
            XU33.ColIndex = 45;
            var XU33121 = header.AddChild("凯鑫万里汇TOB美元账户", new LocaleValue("U凯鑫-万里汇TOB"), SqlStorageType.SqlDecimal);
            XU33121.ColIndex = 46;
            XU33121.Width = 120;
            var XU3312 = header.AddChild("凯鑫万里汇美元账户", new LocaleValue("U凯鑫-万里汇"), SqlStorageType.SqlDecimal);
            XU3312.ColIndex = 47;
            var XU3313 = header.AddChild("香港舰长中行美元账户", new LocaleValue("U香港舰长-中行"), SqlStorageType.SqlDecimal);
            XU3313.ColIndex = 48;
            //var XU34 = header.AddChild("兆诺稠州银行美元账户", new LocaleValue("U兆诺-稠州银行"), SqlStorageType.SqlDecimal);
            //XU34.ColIndex = 390;
            //var XU35 = header.AddChild("辽阔号招行美元账户", new LocaleValue("U辽阔号-招行"), SqlStorageType.SqlDecimal);
            //XU35.ColIndex = 400;
            var XU3501 = header.AddChild("广州诺时工行美元账户", new LocaleValue("U诺时-工行"), SqlStorageType.SqlDecimal);
            XU3501.ColIndex = 49;
            var XU3502 = header.AddChild("海狸招行美元账户", new LocaleValue("U海狸-招行"), SqlStorageType.SqlDecimal);
            XU3502.ColIndex = 50;
            //var XU36 = header.AddChild("广东华于农行人名币", new LocaleValue("R广东华于-农行"), SqlStorageType.SqlDecimal);
            //XU36.ColIndex = 420;
            var XU376 = header.AddChild("香港舰长中行人名币未提现", new LocaleValue("R香港舰长-中行"), SqlStorageType.SqlDecimal);
            XU376.ColIndex = 51;
            var XU37 = header.AddChild("广州诺时工行人名币", new LocaleValue("R诺时-工行"), SqlStorageType.SqlDecimal);
            XU37.ColIndex = 52;
            var XU3711 = header.AddChild("诺时中行人民币", new LocaleValue("R诺时-中行"), SqlStorageType.SqlDecimal);
            XU3711.ColIndex = 53;
            var XU371 = header.AddChild("广州诺时招行人名币", new LocaleValue("R诺时-招行"), SqlStorageType.SqlDecimal);
            XU371.ColIndex = 54;
            var XU372 = header.AddChild("进取号工行人名币", new LocaleValue("R进取号-工行"), SqlStorageType.SqlDecimal);
            XU372.ColIndex = 55;
            var XU373 = header.AddChild("苍穹号招行人名币", new LocaleValue("R苍穹号-招行"), SqlStorageType.SqlDecimal);
            XU373.ColIndex = 56;
            var XU374 = header.AddChild("荣耀号工行人名币", new LocaleValue("R荣耀号-工行"), SqlStorageType.SqlDecimal);
            XU374.ColIndex = 57;
            var XU378 = header.AddChild("锦程万里汇美元", new LocaleValue("U锦程-万里汇"), SqlStorageType.SqlDecimal);
            XU378.ColIndex = 58;
            var XU377 = header.AddChild("锦程万里汇人民币", new LocaleValue("R锦程-万里汇"), SqlStorageType.SqlDecimal);
            XU377.ColIndex = 59;
            var XU375 = header.AddChild("南宁舰长招行人民币", new LocaleValue("R南宁舰长-招行"), SqlStorageType.SqlDecimal);
            XU375.ColIndex = 60;
            var XU38 = header.AddChild("未到账合计金额人名币", new LocaleValue("RMB合计（未到账）"), SqlStorageType.SqlDecimal);
            XU38.ColIndex = 61;
            XU38.Width = 120;
            var XH24111 = header.AddChild("空白列2", new LocaleValue(""));
            XH24111.ColIndex = 62;
            XH24111.Width = 20;

            var XU510 = header.AddChild("凯鑫万里汇余额", new LocaleValue("凯鑫-万里汇"), SqlStorageType.SqlDecimal);
            XU510.ColIndex = 63;
            XU510.Width = 90;
            var XU50 = header.AddChild("Ksher账户余额", new LocaleValue("Ksher"), SqlStorageType.SqlDecimal);
            XU50.ColIndex = 64;
            XU50.Width = 90;
            var XU509 = header.AddChild("寻汇账户余额", new LocaleValue("寻汇"), SqlStorageType.SqlDecimal);
            XU509.ColIndex = 65;
            XU509.Width = 90;
            var XU51 = header.AddChild("pingpong账户余额", new LocaleValue("pingpong"), SqlStorageType.SqlDecimal);
            XU51.ColIndex = 66;
            XU51.Width = 90;
            var XU53 = header.AddChild("连连账户余额", new LocaleValue("连连"), SqlStorageType.SqlDecimal);
            XU53.ColIndex = 67;
            XU53.Width = 90;
            var XU508 = header.AddChild("珊瑚账户余额", new LocaleValue("珊瑚"), SqlStorageType.SqlDecimal);
            XU508.ColIndex = 68;
            XU508.Width = 90;
            var XU52 = header.AddChild("pyvio账户余额", new LocaleValue("pyvio"), SqlStorageType.SqlDecimal);
            XU52.ColIndex = 69;
            XU52.Width = 90;
            var XU520 = header.AddChild("MY202马币2", new LocaleValue("MY202-马币"), SqlStorageType.SqlDecimal);
            XU520.ColIndex = 70;
            XU520.Width = 90;
            var XU521 = header.AddChild("MY214马币2", new LocaleValue("MY214-马币"), SqlStorageType.SqlDecimal);
            XU521.ColIndex = 71;
            XU521.Width = 90;
            var XU522 = header.AddChild("MY201马币2", new LocaleValue("MY201-马币"), SqlStorageType.SqlDecimal);
            XU522.ColIndex = 72;
            XU522.Width = 90;
            var XU523 = header.AddChild("MY213马币2", new LocaleValue("MY213-马币"), SqlStorageType.SqlDecimal);
            XU523.ColIndex = 73;
            XU523.Width = 90;
            var XU524 = header.AddChild("MY103马币2", new LocaleValue("MY103-马币"), SqlStorageType.SqlDecimal);
            XU524.ColIndex = 74;
            XU524.Width = 90;
            var XU525 = header.AddChild("MY102马币2", new LocaleValue("MY102-马币"), SqlStorageType.SqlDecimal);
            XU525.ColIndex = 75;
            XU525.Width = 90;
            var XU526 = header.AddChild("MY102美元2", new LocaleValue("MY102-美元"), SqlStorageType.SqlDecimal);
            XU526.ColIndex = 76;
            XU526.Width = 90;
            var XU54 = header.AddChild("本币合计已到账平台", new LocaleValue("本币合计（已到账平台）"), SqlStorageType.SqlDecimal);
            XU54.ColIndex = 77;
            XU54.Width = 150;
            var XU56 = header.AddChild("平台账户人民币合计", new LocaleValue("RMB合计（已到账平台）"), SqlStorageType.SqlDecimal);
            XU56.ColIndex = 78;
            XU56.Width = 150;
            var XU507 = header.AddChild("空白列4", new LocaleValue(""), SqlStorageType.SqlDecimal);
            XU507.ColIndex = 79;
            XU507.Width = 20;

            var XU501 = header.AddChild("凯鑫万里汇", new LocaleValue("凯鑫-万里汇（未到账）"), SqlStorageType.SqlDecimal);
            XU501.ColIndex = 80;
            XU501.Width = 140;
            var XU502 = header.AddChild("Ksher账户", new LocaleValue("Ksher（未到账）"), SqlStorageType.SqlDecimal);
            XU502.ColIndex = 81;
            XU502.Width = 110;
            var XU45 = header.AddChild("寻汇账户", new LocaleValue("寻汇（未到账）"), SqlStorageType.SqlDecimal);
            XU45.ColIndex = 82;
            XU45.Width = 100;
            var XU503 = header.AddChild("pingpong账户", new LocaleValue("pingpong（未到账）"), SqlStorageType.SqlDecimal);
            XU503.ColIndex = 83;
            XU503.Width = 130;
            var XU505 = header.AddChild("连连账户", new LocaleValue("连连（未到账）"), SqlStorageType.SqlDecimal);
            XU505.ColIndex = 84;
            XU505.Width = 100;
            var XU44 = header.AddChild("珊瑚账户", new LocaleValue("珊瑚（未到账）"), SqlStorageType.SqlDecimal);
            XU44.ColIndex = 85;
            XU44.Width = 100;
            var XU504 = header.AddChild("pyvio账户", new LocaleValue("pyvio（未到账）"), SqlStorageType.SqlDecimal);
            XU504.ColIndex = 86;
            XU504.Width = 110;
            var XU5067 = header.AddChild("MY202马币", new LocaleValue("MY202-马币（未到账）"), SqlStorageType.SqlDecimal);
            XU5067.ColIndex = 87;
            XU5067.Width = 110;
            var XU5061 = header.AddChild("MY214马币", new LocaleValue("MY214-马币（未到账）"), SqlStorageType.SqlDecimal);
            XU5061.ColIndex = 88;
            XU5061.Width = 110;
            var XU5062 = header.AddChild("MY201马币", new LocaleValue("MY201-马币（未到账）"), SqlStorageType.SqlDecimal);
            XU5062.ColIndex = 89;
            XU5062.Width = 110;
            var XU5063 = header.AddChild("MY213马币", new LocaleValue("MY213-马币（未到账）"), SqlStorageType.SqlDecimal);
            XU5063.ColIndex = 90;
            XU5063.Width = 110;
            var XU5064 = header.AddChild("MY103马币", new LocaleValue("MY103-马币（未到账）"), SqlStorageType.SqlDecimal);
            XU5064.ColIndex = 91;
            XU5064.Width = 110;
            var XU5065 = header.AddChild("MY102马币", new LocaleValue("MY102-马币（未到账）"), SqlStorageType.SqlDecimal);
            XU5065.ColIndex = 92;
            XU5065.Width = 110;
            var XU5066 = header.AddChild("MY102美元", new LocaleValue("MY102-美元（未到账）"), SqlStorageType.SqlDecimal);
            XU5066.ColIndex = 93;
            XU5066.Width = 110;
            var XU5068 = header.AddChild("本币合计未到账", new LocaleValue("本币合计（未到账）"), SqlStorageType.SqlDecimal);
            XU5068.ColIndex = 94;
            XU5068.Width = 140;
            var XU506 = header.AddChild("未到账合计金额人名币2", new LocaleValue("RMB合计（未到账）"), SqlStorageType.SqlDecimal);
            XU506.ColIndex = 95;
            XU506.Width = 140;
            var XH241 = header.AddChild("空白列", new LocaleValue(""));
            XH241.ColIndex = 96;
            XH241.Width = 20;

            var XH0421 = header.AddChild("广州诺时工行理财", new LocaleValue("R诺时-工行理财"), SqlStorageType.SqlDecimal);
            XH0421.ColIndex = 97;
            XH0421.Width = 100;
            var XH281 = header.AddChild("广州舰长工行理财", new LocaleValue("R舰长-工行理财"), SqlStorageType.SqlDecimal);
            XH281.ColIndex = 98;
            XH281.Width = 100;
            var XH282 = header.AddChild("进取号工行理财", new LocaleValue("R进取号-工行理财"), SqlStorageType.SqlDecimal);
            XH282.ColIndex = 99;
            XH282.Width = 110;
            var XH283 = header.AddChild("荣耀号工行理财", new LocaleValue("R荣耀号-工行理财"), SqlStorageType.SqlDecimal);
            XH283.ColIndex = 100;
            XH283.Width = 110;
            var XH2833 = header.AddChild("腾海招行理财", new LocaleValue("R腾海-招行理财"), SqlStorageType.SqlDecimal);
            XH2833.ColIndex = 101;
            XH283.Width = 110;
            var XH284 = header.AddChild("万里汇美金理财账户", new LocaleValue("U舰长-万里汇理财"), SqlStorageType.SqlDecimal);
            XH284.ColIndex = 102;
            XH284.Width = 120;
            var XH285 = header.AddChild("理财人民币小计", new LocaleValue("RMB理财小计3"), SqlStorageType.SqlDecimal);
            XH285.ColIndex = 103;
            XH285.Width = 110;
         
            
           
        
            
            

            
          
           
            return header;
        }
        /// <summary>     
        /// /// 设置报表合计列  
        /// /// </summary>    
        /// /// <param name="filter"></param>   
        /// /// <returns></returns>   
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("万里汇", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州诺时工行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("香港舰长中行人民币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM)); 
            result.Add(new SummaryField("广州诺时招行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州诺时中行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("兆诺稠州银行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("广东华于农行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("广州辽阔号招行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("泉州湛蓝号中信", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("吴秋红支付宝", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("徐权龙招行卡", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("谢伟贤平安卡", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("谢伟贤招行卡", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("赵鹏伟平安卡", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("平台佳Susie", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("广州辽阔号中行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州荣耀号工行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州进取号工行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广东海狸招行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州苍穹号招行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广东腾海招行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("人名币小计", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("香港舰长汇丰", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("香港舰长中行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("万里汇美金账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("美元小计", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("本位币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("付款单", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("收款单", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("香港舰长汇丰美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("万里汇美金账户美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("兆诺稠州银行美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("万里汇美金理财账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("辽阔号招行美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("广东华于农行人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州诺时工行人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("香港舰长中行人名币未提现", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM)); 
            result.Add(new SummaryField("广州诺时招行人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("进取号工行人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("苍穹号招行人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("荣耀号工行人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("南宁舰长招行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("诺时中行人民币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("未到账合计金额人名币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FUNPAIDAMOUNT7", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FUNPAIDAMOUNT15", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FUNPAIDAMOUNT", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FWFJE", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("诚意赊", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州诺时工行理财", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州舰长工行", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("南宁舰长招行人民币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("本位币合计", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州诺时工行美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("海狸招行美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇人民币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇美元", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇TOB美元", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇TOB美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("香港舰长中行美元账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("丰顺金晨", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("舰长688诚意赊", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("舰长988诚意赊", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("珊瑚菲律宾比索", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("珊瑚人民币账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("珊瑚越南盾", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("寻汇菲律宾比索", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("寻汇新加坡元", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            //result.Add(new SummaryField("寻汇印尼盾", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
          
            result.Add(new SummaryField("珊瑚账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("寻汇账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("Ksher账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("pingpong账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("pyvio账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("连连账户", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("未到账合计金额人名币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("本币合计未到账", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("Ksher账户余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("pyvio账户余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("连连账户余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("pingpong账户余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("珊瑚账户余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("寻汇账户余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("凯鑫万里汇余额", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("平台账户人民币合计", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("本币合计已到账平台", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("广州舰长工行理财", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("进取号工行理财", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("荣耀号工行理财", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("腾海招行理财", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("理财人民币小计", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("诺时民泰", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("锦程万里汇人民币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("锦程万里汇美元", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("锦程万里汇人民币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("锦程万里汇美元2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY202马币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY214马币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY201马币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY213马币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY103马币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY102马币", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY102美元", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY202马币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY214马币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY201马币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY213马币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY103马币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY102马币2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("MY102美元2", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            return result;
        }
        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
        
    }
}
